import { Router } from "express"
import { IcallbackFn, Ioracle } from "../../types"
import { BindParameters } from "oracledb"
import { decrypt } from "../../utils/privateKey"
import oracle from "../../oracle"
import { sendMail } from "./mail"

interface docinfo {
  docid: string
  current_flow: number
  username: string
  approve: string
  comments: string
  created_time: string
  replace_flag: string
}
interface approveitem {
  material_origina: string
  material_replacea: string
  material_product: string
  replace_reason: string
  replace_comments: string
  current_flow: number
  unit: string
  amount: number
  start_time: string
  end_time?: string
  org_code: string
  apply_seq: number
}

async function mailApprove(flow: number, docid: string) {
  return new Promise((resolve, reject) => {
    let sql =
      " select b.chinese_name,substr(a.klm,9)||'@compeq.huizhou.gd.cn' email from xx_material_replace_apply_flow a,user_info_n b"
    sql += ` where a.flow_code = '${
      flow === 70 ? 10 : flow + 10
    }' and a.klm = b.account and a.docid = '${docid}'`
    oracle
      .connChaos({ sql })
      .then((result) => {
        if (result.data.length > 0) {
          const { chinese_name, email } = result.data[0]
          sendMail({
            mailTo: email as string,
            mailSubject:
              flow === 70
                ? "物料替代系統-簽核結案通知"
                : "物料替代系統-簽核通知郵件",
            mailHtml:
              flow === 70
                ? "./mail/material_substitution/send_complete.ejs"
                : "./mail/material_substitution/send_approve.ejs",
            mailData: { chinese_name, email },
          })
            .then(() => {
              resolve({ code: 200, message: "送出成功" })
            })
            .catch((err) => reject(err))
        }
      })
      .catch((err) => {
        reject(err)
      })
  })
}

export default (router: Router, oracle: Ioracle, callbackFn: IcallbackFn) => {
  router.post("/getML10docid", (req, res) => {
    let { username } = req.body
    username = decrypt(username)
    let sql = `select distinct a.docid,a.current_flow from xx_material_replace_apply a,xx_material_replace_apply_flow b`
    sql += ` where a.docid = b.docid and a.current_flow = b.flow_code and b.klm = '${username}'`
    sql += ` and a.current_flow <> 10`
    callbackFn(res, oracle.connChaos, { sql })
  })

  router.post("/getML10detail", (req, res) => {
    const { docid } = req.body
    let sql = `select * from xx_material_replace_apply where docid = '${docid}'`
    callbackFn(res, oracle.connChaos, { sql })
  })

  router.post("/Approve", (req, res) => {
    const { docinfo, docdetail } = req.body
    const docinfoPar: docinfo = JSON.parse(docinfo) //單號信息，用來更新流程表
    const approveitems: Array<approveitem> = JSON.parse(docdetail) //填入信息，用來更新M10/L10用戶輸入

    //更新流程
    let sql = ` update xx_material_replace_apply_flow set approve = '${docinfoPar.approve}',comments='${docinfoPar.comments}'`
    sql += ` ,last_update = sysdate`
    sql += ` where docid = '${docinfoPar.docid}' and flow_code = ${docinfoPar.current_flow}`
    oracle
      .connChaos({ sql })
      .then((result) => {
        if (result) {
          //更新單子數據
          if (docinfoPar.current_flow === 10) {
            sql = ` update xx_material_replace_apply set material_origina = :material_origina,material_replacea=:material_replacea`
            sql += ` ,material_product=:material_product,replace_reason = :replace_reason,replace_comments=:replace_comments`
            sql += ` ,current_flow_phase = (select flow_phase from xx_material_replace_apply_flow where docid = :docid and flow_code = :current_flow)`
            sql += ` ,last_update_by = :username,last_update_time=sysdate,current_flow=:current_flow`
            sql += ` ,unit = :unit,amount=:amount,start_time=to_date(:start_time,'YYYY-MM-DD')`
            sql += ` where docid = :docid and apply_seq = :apply_seq`
            //一個條件不夠，需帶入之前的信息
            let bind: Array<BindParameters> = []
            approveitems.forEach((item) => {
              let temp = {
                material_origina: item.material_origina,
                material_replacea: item.material_replacea,
                material_product: item.material_product,
                replace_reason: item.replace_reason,
                replace_comments: item.replace_comments,
                unit: item.unit,
                amount: item.amount,
                start_time: item.start_time,
                apply_seq: item.apply_seq,
                username: decrypt(docinfoPar.username),
                docid: docinfoPar.docid,
                current_flow: item.current_flow + 10,
              }
              bind.push(temp)
            })
            oracle
              .connChaos({ sql, isMulti: true, params: bind })
              .then((result) => {
                if (result.code === 200) {
                  mailApprove(docinfoPar.current_flow, docinfoPar.docid)
                    .then(() => {
                      res.send({ code: 200, message: "送出成功！", data: [] })
                    })
                    .catch(() => {
                      res.send({
                        code: 0,
                        message: "單據已正常送出！郵件發送異常！",
                      })
                    })
                }
              })
              .catch(() => {
                res.send({ code: 0, message: "數據庫異常！" })
              })
          } else if (docinfoPar.current_flow === 60) {
            //L20流程單子更新
            sql = ` update xx_material_replace_apply `
            sql += ` set current_flow=:current_flow`
            sql += ` ,end_time=to_date(:end_time,'yyyy-mm-dd')`
            sql += ` ,current_flow_phase = (select flow_phase from xx_material_replace_apply_flow where docid = :docid and flow_code = :current_flow)`
            sql += ` ,last_update_by = :username ,last_update_time = sysdate`
            sql += ` where docid = :docid and material_origina=:material_origina and material_replacea=:material_replacea`
            let bind: Array<BindParameters> = []
            approveitems.forEach((item) => {
              let temp1 = {
                docid: docinfoPar.docid,
                username: decrypt(docinfoPar.username),
              }
              let temp2 = {
                material_origina: item.material_origina,
                material_replacea: item.material_replacea,
                current_flow: item.current_flow + 10,
              }
              //L20審核才需要設置end_time。
              let temp3 = {
                end_time: item.end_time,
              }
              temp2 = { ...temp1, ...temp2 }
              if (docinfoPar.current_flow === 60) {
                temp2 = { ...temp2, ...temp3 }
              }
              bind.push(temp2)
            })
            oracle
              .connChaos({ sql, isMulti: true, params: bind })
              .then((result) => {
                if (result.code === 200) {
                  mailApprove(docinfoPar.current_flow, docinfoPar.docid)
                    .then(() => {
                      res.send({ code: 200, message: "送出成功！", data: [] })
                    })
                    .catch(() => {
                      res.send({
                        code: 0,
                        message: "單據已正常送出！郵件發送異常！",
                      })
                    })
                }
              })
              .catch(() => {
                res.send({ code: 0, message: "數據庫異常！" })
              })
          } else if (docinfoPar.current_flow === 40) {
            //流程為40需要進行額外操作
            let sqlupdate = `update xx_material_replace_apply set current_flow = ${
              docinfoPar.current_flow === 40 && docinfoPar.replace_flag === "Y"
                ? docinfoPar.current_flow + 5
                : docinfoPar.current_flow + 10
            }`
            sqlupdate += ` ,last_update_by='${decrypt(
              docinfoPar.username
            )}',last_update_time = sysdate`
            //由於是先更新到表單後插入的數據，如果根據流程選flowPhase會為null
            if (docinfoPar.replace_flag === "N") {
              sqlupdate += ` ,current_flow_phase = (select flow_phase from xx_material_replace_apply_flow where docid ='${docinfoPar.docid}'`
              sqlupdate += ` and flow_code = ${docinfoPar.current_flow + 10}),`
            } else {
              sqlupdate += ` ,current_flow_phase = 'L20經辦-1',`
            }
            sqlupdate += `replace_flag = '${docinfoPar.replace_flag}'`
            sqlupdate += ` where docid = '${docinfoPar.docid}'`

            oracle
              .connChaos({ sql: sqlupdate })
              .then(() => {
                if (docinfoPar.replace_flag === "Y") {
                  let sqlquery = `select count(*) count from xx_material_replace_apply_flow where docid = '${docinfoPar.docid}' and flow_code =45`
                  //如果已經存在一條則無需添加，直接切換即可 //流程為40時需要檢查用戶是否設置替換BOM，如果是，則增加一條45的流程--L20經辦
                  oracle
                    .connChaos({ sql: sqlquery })
                    .then((result) => {
                      if (result.data[0].count === 0) {
                        let sqlInsert = `insert into xx_material_replace_apply_flow values('${docinfoPar.docid}',45,'L20經辦-1',sysdate,`
                        sqlInsert += ` 'xdomain\\wenbiaodeng','','','')`
                        oracle
                          .connChaos({ sql: sqlInsert })
                          .then(() => {
                            //這裏發送郵件流程要置為35
                            mailApprove(35, docinfoPar.docid)
                              .then(() => {
                                res.send({
                                  code: 200,
                                  message: "送出成功！",
                                  data: [],
                                })
                              })
                              .catch(() => {
                                res.send({
                                  code: 0,
                                  message: "單據已正常送出！郵件發送異常！",
                                })
                              })
                          })
                          .catch(() => {
                            res.send({ code: 0, message: "數據庫異常！" })
                          })
                      } else {
                        mailApprove(docinfoPar.current_flow, docinfoPar.docid)
                          .then(() => {
                            res.send({
                              code: 200,
                              message: "送出成功！",
                              data: [],
                            })
                          })
                          .catch(() => {
                            res.send({
                              code: 0,
                              message: "單據已正常送出！郵件發送異常！",
                            })
                          })
                      }
                    })
                    .catch(() => {
                      res.send({ code: 0, message: "數據庫異常！" })
                    })
                } else {
                  //此處不經過BOM替換，直接發送到50就好
                  mailApprove(40, docinfoPar.docid)
                    .then(() => {
                      res.send({
                        code: 200,
                        message: "送出成功！",
                        data: [],
                      })
                    })
                    .catch(() => {
                      res.send({
                        code: 0,
                        message: "單據已正常送出！郵件發送異常！",
                      })
                    })
                }
              })
              .catch(() => {
                res.send({ code: 0, message: "數據庫異常！" })
              })
          } else {
            sql = `update xx_material_replace_apply set current_flow = ${
              docinfoPar.current_flow === 45 && docinfoPar.replace_flag === "Y"
                ? docinfoPar.current_flow + 5
                : docinfoPar.current_flow + 10
            }`
            sql += ` ,last_update_by='${decrypt(
              docinfoPar.username
            )}',last_update_time = sysdate`
            //流程為70直接結案--不用進行其他的操作了，更新完表格之後直接發送郵件，結束
            if (docinfoPar.current_flow === 70) {
              sql += ` ,current_flow_phase = '已結案',end_status = 'Y'`
            }
            //L20--經辦,下一階為L00審核，也不用進行其他的操作,更新完後發送郵件即可
            else if (docinfoPar.current_flow === 45) {
              sql += ` ,current_flow_phase = (select flow_phase from xx_material_replace_apply_flow where docid = '${
                docinfoPar.docid
              }' and flow_code = ${docinfoPar.current_flow + 5})`
            }
            //其餘流程無需特殊動作
            else {
              sql += ` ,current_flow_phase = (select flow_phase from xx_material_replace_apply_flow where docid = '${
                docinfoPar.docid
              }' and flow_code = ${docinfoPar.current_flow + 10})`
            }
            sql += ` where docid = '${docinfoPar.docid}'`
            oracle
              .connChaos({ sql })
              .then((result) => {
                if (result.code === 200) {
                  mailApprove(
                    docinfoPar.current_flow === 45
                      ? 40
                      : docinfoPar.current_flow,
                    docinfoPar.docid
                  )
                    .then(() => {
                      //2024-12-06 修改：50审核时需多加一条，当替换flag为N时，直接结案掉流程
                      if (docinfoPar.current_flow === 50) {
                        sql = `update xx_material_replace_apply set end_status = 'Y' where docid = '${docinfoPar.docid}' and replace_flag='N'`
                        oracle
                          .connChaos({ sql })
                          .then((result) => {
                            if (result.code === 200) {
                              res.send({
                                code: 200,
                                message: "送出成功！",
                                data: [],
                              })
                            }
                          })
                          .catch(() => {
                            res.send({
                              code: 0,
                              message: "單據已正常送出！結案狀態修改異常！",
                            })
                          })
                      } else {
                        res.send({ code: 200, message: "送出成功！", data: [] })
                      }
                    })
                    .catch(() => {
                      res.send({
                        code: 0,
                        message: "單據已正常送出！郵件發送異常！",
                      })
                    })
                }
              })
              .catch(() => {
                res.send({ code: 0, message: "數據庫異常！" })
              })
          }
        }
      })
      .catch(() => {
        res.send({ code: 0, message: "數據庫異常！" })
      })
  })

  router.post("/Reject", (req, res) => {
    const { info } = req.body
    let infoPar = JSON.parse(info)
    //先更新流程表
    let sql = ` update xx_material_replace_apply_flow set approve='N',comments = '${infoPar.comments}',last_update=sysdate  `
    sql += ` where docid = '${infoPar.docid}' and flow_code=${infoPar.current_flow}`
    oracle
      .connChaos({ sql })
      .then(() => {
        sql = ` update xx_material_replace_apply set current_flow = ${infoPar.backflow}`
        sql += ` ,current_flow_phase = (select flow_phase from xx_material_replace_apply_flow where docid = '${infoPar.docid}' and flow_code = ${infoPar.backflow})`
        sql += ` ,last_update_by = '${decrypt(
          infoPar.username
        )}',last_update_time = sysdate`
        sql += ` where docid = '${infoPar.docid}'`
        oracle
          .connChaos({ sql })
          .then((result) => {
            if (result.code === 200) {
              //獲取即將要發送郵件的信息
              sql = `select  (select flow_phase from xx_material_replace_apply_flow where docid = '${infoPar.docid}' and flow_code = ${infoPar.backflow}) back_flow`
              sql += ` ,(select b.chinese_name from xx_material_replace_apply_flow a,user_info_n b `
              sql += ` where a.docid = '${infoPar.docid}' and a.flow_code = ${infoPar.backflow} and a.klm = b.account) chinese_name`
              sql += ` ,(select substr(klm,9)||'@compeq.huizhou.gd.cn' from xx_material_replace_apply_flow`
              sql += ` where docid = '${infoPar.docid}' and flow_code = ${infoPar.backflow}) back_flow_email`
              sql += ` ,(select flow_phase from xx_material_replace_apply_flow where docid = '${infoPar.docid}' and flow_code = ${infoPar.current_flow}) current_flow`
              sql += ` ,(select comments from xx_material_replace_apply_flow where docid = '${infoPar.docid}' and flow_code = ${infoPar.current_flow}) comments`
              sql += " from dual"
              oracle
                .connChaos({ sql })
                .then((result) => {
                  if (result.code === 200) {
                    const {
                      back_flow,
                      chinese_name,
                      back_flow_email,
                      current_flow,
                      comments,
                    } = result.data[0]
                    sendMail({
                      mailTo: "xiaowei1@compeq.huizhou.gd.cn",
                      mailSubject: "物料替代系統-簽核退回通知",
                      mailHtml: "./mail/material_substitution/send_reject.ejs",
                      mailData: {
                        back_flow,
                        chinese_name,
                        back_flow_email,
                        current_flow,
                        comments,
                      },
                    })
                      .then(() => {
                        res.send({ code: 200, message: "送出成功", data: [] })
                      })
                      .catch(() => {
                        res.send({
                          code: 0,
                          message: "單據已正常送出！郵件發送異常！",
                        })
                      })
                  }
                })
                .catch(() => {
                  res.send({ code: 0, message: "數據庫異常！" })
                })
            }
          })
          .catch(() => {
            res.send({ code: 0, message: "數據庫異常！" })
          })
      })
      .catch(() => {
        res.send({ code: 0, message: "數據庫異常！" })
      })
  })

  return router
}
